<?php
class Shepherd_Model_Mapper_Account extends Shepherd_Model_Mapper_Abstract
{
	/**
	 * Get Db table object
	 *
	 * @return Model_DbTable_Inventory
	 */
	protected $_dbName;

	public function __construct()
	{
		parent::__construct();
		$this->_dbName = $this->getCurrentShepherdSchema().'.accounts';
	}
	
	/**
	 * Get Db table object
	 *
	 * @return Model_DbTable_Account
	 */
	public function getDbTable($instanceId = null)
	{
		if (null === $this->_dbTable) {
			$this->setDbTable('Shepherd_Model_DbTable_Account',$instanceId);
		}
		return $this->_dbTable;
	}
	
	/**
	 * Map row object data to the site object properties
	 *
	 * @param  Zend_Db_Table_Row $row
	 * @throws Exception
	 * @return Model_Account mapped object
	 */
	public function mapData($row)
	{
		if (!$row instanceof Zend_Db_Table_Row) {
			throw new Exception('Invalid table row provided');
		}
		$user = new Shepherd_Model_Account();
		$user->setAccountId($row['accountid'])
		->setUsername($row['username'])
		->setPassword($row['password'])
		->setFullname($row['fullname'])
		->setEmail($row['email'])
		->setIsActivated($row['isactivated'])
		->setIsBlock($row['isblock'])
		->setIsSystem($row['issystem'])
		->setIsArchived($row['isarchived'])
		->setSalt($row['salt'])
		->setAddress($row['address'])
		->setPhone($row['phone'])
		->setActivateCode($row['activatecode'])
		->setOtrsUserId($row['otrs_user_id'])
		->setCustomerIds($row['customer_ids']);
		return $user;
	}
	
	/**
	 * Save Vendor object in database
	 *
	 * @param Model_Account $account
	 *
	 * @return int The primary key of the row inserted or the number of rows updated
	 */
	public function save($account,$instanceId = null)
	{
		parent::save($account);
		$data = array(
				'accountid'     => $account->getAccountId(),
				'username'      => $account->getUsername(),
				'password'      => $account->getPassword(),
				'fullname'      => $account->getFullname(),
				'email'         => $account->getEmail(),
				'isactivated'   => $account->getIsActivated(),
				'isblock'       => $account->getIsBlock(),
				'issystem'      => $account->getIsSystem(),
				'isarchived'    => $account->getIsArchived(),
				'salt'          => $account->getSalt(),
				'address'       => $account->getAddress(),
				'phone'         => $account->getPhone(),
				'activatecode'  => $account->getActivateCode(),
				'otrs_user_id'  => $account->getOtrsUserId(),
				'customer_ids'  => $account->getCustomerIds()
		);
		try {
			if (NULL === ($id = $account->getAccountId())) {
				unset($data['accountid']);
				$account->setAccountId($this->getDbTable($instanceId)->insert($data));
				return $account->getAccountId();
			} else {
				return $this->getDbTable($instanceId)->update($data, array('accountid = ?' => $id));
			}
		} catch (Exception $e) {
			Sss_Ultility_Common::logger()->log($e, Zend_Log::ERR);
		}
	}
	
	/**
	 * Fetch all Account
	 *
	 * @return array List of Model_Account
	 */
	public function fetchAll($instanceId = null)
	{
		$resultSet = $this->getDbTable($instanceId)->fetchAll();
		$entries   = array();
		foreach ($resultSet as $row) {
			$entries[] = $this->mapData($row);
		}
		return $entries;
	}
	
	/**
	 * Fetch a Site Account by Identifier
	 *
	 * @param $id
	 *
	 * @return Model_Account
	 */
	public function findById($id,$instanceId = null)
	{
		$select	= $this->getDbTable($instanceId)->select()->where('accountid = ?', $id);
		$row = $this->getDbTable($instanceId)->fetchRow($select);
		$entry   = NULL;
		if (!empty($row)) {
			$entry = $this->mapData($row);
		}
		return $entry;
	}
	
	public function deleteRow($accountId,$instanceId = null)
	{
		return $this->getDbTable($instanceId)->delete(array('accountid = ?' => $accountId));
	}
	
	public function getUser($userName = null, $instanceId=null)
	{
		$where= array();
		$accountTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.accounts';
		if($instanceId == null)
			$accountTableName = $this->_dbName;
		if($userName != null)
		$where[] = " isactivated = 1 ";
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$query = $this->_db->quoteInto("
						SELECT 
							* 
						FROM 
							".$accountTableName.$where." 
							AND username = ?",$userName
				);
		$user = $this->fetchObject($query);
		return $user;
	}
	
	public function getUserByEmail($email = null)
	{
		$where= array();
		if($email != null)
			$where[] = " email = '".$email."'";
		$where[] = " isactivated = 1 ";
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$user = $this->fetchObject("
							SELECT 
								* 
							FROM 
								".$this->_dbName
							.$where
		);
		return $user;
	}
	
	/**
	* Get user select 
	* @param array $params
	* @return Zend_Db_Table_Select
	*/
	public function getList($limit = '', $limitstart = 0, $filterParam = array(), $isList = null)
	{
		$where = array();
		if ($filterParam['username']) {
			$where[] = 'u.username LIKE "%' . $filterParam['username'].'%"';
		}
		if ($filterParam['fullname']) {
			$where[] = 'u.fullname LIKE "%' . $filterParam['fullname'].'%"';
		}
		if ($filterParam['rolename']) {
			$where[] = 'r.rolename LIKE "%' . $filterParam['rolename'].'%"';
		}
		if($isList != null){
			$where[] = " u.username <> '".Sss_Ultility_CommonViewParamater::USER_NAME_SHEPHERD_DEFAULT."'";
		}
		if (isset($filterParam['isactivated'])) {
			$where[] = ' u.isactivated = '.$filterParam['isactivated'];
		}
		$where[] = " isarchived = 0 ";

		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';

		$getPagination = Sss_Ultility_Common::getPagination($limit, $limitstart, $filterParam['orderfield'],$filterParam['ordertype'],$filterParam['page'], 'accountid');
		$orderBy    = $getPagination['orderby'];
		$limit      = $getPagination['limit'];
		$limitstart = $getPagination['limitstart'];
		$pagString  = $getPagination['pagstring'];

		$rows = $this->loadAll('
						SELECT 
							* 
						FROM 
							' . $this->_dbName .' u 
						lEFT JOIN 
							'.$this->getCurrentShepherdSchema().'.assignments a ON a.account = u.accountid
						LEFT JOIN 
							'.$this->getCurrentShepherdSchema().'.roles r ON r.roleid = a.role'
						. $where . $orderBy.$pagString
						, true
		);

		$rowAll = $this->loadAll('
						SELECT 
							* 
						FROM 
							'. $this->_dbName .' u 
						LEFT JOIN '.$this->getCurrentShepherdSchema().'.assignments a ON a.account = u.accountid
						LEFT JOIN '.$this->getCurrentShepherdSchema().'.roles r ON r.roleid = a.role'
						. $where 
						. $orderBy
						, true
		);
		$result = new stdclass();
		//return pagination object
		$result->pagination = Sss_Grid::createPagiObject($limit, $limitstart, count($rowAll));

		//return list
		$result->list = $rows;

		return $result;
	}
	
	public function getById($accountId= null)
	{
		$where= array();
		if($accountId != null){
			$where[] = " accountid =".$accountId;
		}
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$user = $this->fetchObject("
							SELECT 
								a.*, r.roleid, r.rolename 
							FROM
								".$this->_dbName.' a 
								INNER JOIN '.$this->getCurrentShepherdSchema().'.assignments ass ON ass.account = a.accountid
								INNER JOIN '.$this->getCurrentShepherdSchema().'.roles r ON r.roleid = ass.role'
							.$where
		);
		return $user;
	}

	public function getUserList($userId = null)
	{
		$where = array();
		if($userId != null){
			$where[] = ' accountid NOT IN('.$userId.')';
			$where[] = " username <> '".Sss_Ultility_CommonViewParamater::USER_NAME_SHEPHERD_DEFAULT."'";
		}
		$where[] = " isactivated = 1 ";
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$rowAll = $this->loadAll('
						SELECT 
							* 
						FROM 
							' . $this->_dbName.$where
		);
		return $rowAll;
	}
	
	public function getUserByActiveCode($activeCode, $instanceId=null)
	{

		if (is_null($instanceId)) {
			$this->_dbName = $this->getCurrentShepherdSchema().'.accounts';
		} else {
			$this->_dbName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.accounts';

			if (!$this->checkExistedShepherdDatabase($instanceId)) {
				return false;
			}
		}
		$where = array();
		$where[] = " activatecode= '".$activeCode."'";
		$where[] = " isarchived = 0 ";
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$user = $this->fetchObject("
							SELECT 
								* 
							FROM 
								".$this->_dbName
							.$where
							, true
							, $instanceId
		);
		return $user;

	}

	public function checkEsixtedColumns($columnName = '', $instanceId=null) 
	{
		$databaseName = $this->getCurrentShepherdSchema($instanceId);
		$query = "
			SHOW COLUMNS 
			FROM 
				$databaseName.`accounts` 
			LIKE 
				'".$columnName."'";
		$res = $this->fetchObject($query, true, $instanceId);
		if($res == false)
		{
			$addCol = "
				ALTER TABLE 
					$databaseName.`accounts` 
				ADD COLUMN 
					".$columnName." VARCHAR(255)";
			$this->executeQuery($addCol, true, $instanceId);
		}
		return $res;
	}
	
	function checkUserInRole($role){  
		$where = ' WHERE r.roleid= '.$role;
		$user = $this->fetchObject("
							SELECT 
								* 
							FROM 
								".$this->_dbName.' u '.'
								LEFT JOIN '.$this->getCurrentShepherdSchema().'.assignments a ON a.account = u.accountid
								LEFT JOIN '.$this->getCurrentShepherdSchema().'.roles r ON r.roleid = a.role '.$where, true
		);
		return $user;
	}

	public function getListuser()
	{
		$query = $this->loadAll("
						SELECT 
							* 
						FROM 
							".$this->_dbName." 
						WHERE 
							isactivated = 1 
						ORDER BY 
							accountid 
						DESC"
		);
		return $query;
	}
	
	public function getUserDefault()
	{
		$query = "
					SELECT 
						accountid 
					FROM 
						".$this->_dbName." 
					WHERE 
						isactivated = 1 
					ORDER BY 
						accountid 
					DESC 
					LIMIT 1";
		$query = $this->fetchObject($query);
		return $query;
	}
	
	public function getUserById($accountId, $instanceId)
	{
		$accountTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.accounts';
		$assignmentsTableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.assignments';
		if($accountId && $accountId != null){
			$where[] = " accountid =".$accountId;
		}
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';
		$user = $this->fetchObject("
					SELECT 
						a.username,a.fullname,b.role 
					FROM 
						".$accountTableName." a 
						INNER JOIN ".$assignmentsTableName." b ON b.account = accountid".$where
		);
		return $user;
	}
}
